package com.zhuxulin.java.email.db;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.zhuxulin.java.email.db.bean.QQRowBean;

/**
 * 数据库查询，功能未完全实现
 * 
 * @author ZhuXulin
 * 
 */
public class DBQuery {
	private Connection conn = null;
	private ResultSet rs = null;
	private Statement statement = null;
	private static DBQuery db = new DBQuery();

	private DBQuery() {
		initConn();
	}

	public static DBQuery getInstance() {
		return db;
	}

	private void initConn() {
		try {
			conn = DriverManager.getConnection(DbConfig.url, DbConfig.user, DbConfig.password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean isExit(String mail) throws SQLException, UnsupportedEncodingException {
		// 连续数据库
		if (conn.isClosed()) {
			return false;
		}
		// statement用来执行SQL语句
		Statement statement = conn.createStatement();
		// 要执行的SQL语句
		String sql = "select * from send where mail='" + mail + "'";
		// 结果集
		ResultSet rs = statement.executeQuery(sql);

		String name = null;
		QQRowBean bean = null;
		// while (rs.next()) {
		// bean = parseQQRowBean(rs);
		// }
		if (rs.next()) {
			rs.close();
			return true;
		} else {
			rs.close();
			return false;
		}
	}

	public boolean setLastSendDate(String mail, long sendDate) {
		String sql = "update send set last_send_time='" + sendDate + "' where mail='" + mail + "'";
		System.out.println(sql);
		try {
			statement = conn.createStatement();
			return statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean setLastSendDate(String mail, Date date) {
		return setLastSendDate(mail, date.getTime());
	}

	public boolean setSendTimes(String mail, int times) {
		String sql = "update send set send_times=" + times + " where mail='" + mail + "'";
		try {
			statement = conn.createStatement();
			return statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean addSendTimes(String mail) {
		return setSendTimes(mail, getQQRowBean(mail).getTimes() + 1);
	}

	public int insertQQRowBean(QQRowBean bean) {
		String sql = "INSERT INTO send(mail,last_send_time,send_times) VALUES('" + bean.getMail() + "'," + bean.getLastDate().getTime() + ","
				+ bean.getTimes() + ")";
		try {
			statement = conn.createStatement();
			return statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public int initInsert(QQRowBean bean) {
		String sql = "INSERT INTO send(mail,send_times) VALUES('" + bean.getMail() + "'," + bean.getTimes() + ")";
		System.out.println(sql);
		try {
			statement = conn.createStatement();
			return statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public QQRowBean getQQRowBean(String mail) {
		String sql = "select * from send where mail='" + mail + "'";
		QQRowBean bean = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				bean = parseQQRowBean(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	public Date getLastSendDate(String mail) {
		return getQQRowBean(mail).getLastDate();
	}

	public int getSendTimes(String mail) {
		return getQQRowBean(mail).getTimes();
	}

	private QQRowBean parseQQRowBean(ResultSet rs) throws SQLException {
		QQRowBean bean = new QQRowBean();
		bean.setId(rs.getInt("id"));
		bean.setMail(rs.getString("mail"));
//		System.out.println(rs.getLong("last_send_time"));
		bean.setLastDate(new Date(rs.getLong("last_send_time")));
		bean.setTimes(rs.getInt("send_times"));
		return bean;
	}

	public ArrayList<QQRowBean> getAllList() {
		ArrayList<QQRowBean> beans = new ArrayList<QQRowBean>();
		String sql = "select * from send";
		QQRowBean bean = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				bean = parseQQRowBean(rs);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return beans;

	}

	public void closeDBConn() throws SQLException {
		conn.close();
	}
}
